package com.ch.base.dao;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;

import javax.servlet.http.HttpServletRequest;

import org.apache.struts2.ServletActionContext;

import com.ch.base.db.DBBean;
import com.ch.base.obj.QueryResultObj;
import com.ch.base.util.StringUtils;


public class GenerateCodeDAO extends DBBean {
	private String database;

	public GenerateCodeDAO(Connection con){
		super(con);
		this.setLogger(this.getClass().getName());

		HttpServletRequest request;
		request = ServletActionContext.getRequest();
		String hostMilieu = request.getSession().getServletContext().getInitParameter("hostMilieu");
		ResourceBundle rb = ResourceBundle.getBundle("database");
		database = rb.getString(StringUtils.proper(hostMilieu, "_") + "_" + request.getContextPath().replace("/", "") + "_database");
	}

	public List queryAllTable() throws Exception{
		List result = new ArrayList();
		StringBuffer sbSql = new StringBuffer();
		if (database.equalsIgnoreCase("oracle")) {
			sbSql.append("SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE' ORDER BY OBJECT_NAME"); 
		} else if (database.equalsIgnoreCase("sql server")) {
			sbSql.append("select name as table_name from sysobjects where xtype = 'U' or xtype = 'V' order by name"); 
		}

		try {
			log.info("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), null));
			result = ((QueryResultObj)this.executeQuery(Map.class, sbSql.toString(), null)).getQueryList();			
		} catch (Exception e) {
			log.error("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), null));
			throw e;
		}

		return result;	
	}

	/**
	 * 查詢 query(Wrapper wrapper, String whereCondition, String other)：
	 * 	(1)	此method 回傳之型態為List<Wrapper>
	 * 	(2)	whereCondition 為不在 wrapper 傳入欄位的查詢條件，例如日期區間...等等，由呼叫者組好傳入，前面需含 " and " 做為啟始字串。
	 * 	(3)	other 字串可為 "order by..." 或 "group by..."，當字串不為 null 或 空字串時，取代整段預設 order by / group by 字串。
	 * 注意：(1)SysCodeMainWrapper須置換為所需之wrapper。
	 *       (2)whereCondition 只放 wrpper 沒法取的欄位
	 * @param wra
	 * @param whereCondition
	 * @param other
	 * @return
	 * @throws Exception
	 */
	public List<GenerateCodeWrapper> query(String tableName) throws Exception{
		List<GenerateCodeWrapper> result = new ArrayList<GenerateCodeWrapper>();
		StringBuffer sbSql = new StringBuffer();
		ArrayList al = new ArrayList();
		if (database.equalsIgnoreCase("oracle")) {
			sbSql.append("SELECT T.TABLE_NAME, ");
			sbSql.append("       T.COLUMN_ID, ");
			sbSql.append("       T.COLUMN_NAME, ");
			sbSql.append("       T.DATA_TYPE, ");
			sbSql.append("       T.CHAR_LENGTH, ");
			sbSql.append("       T1.COMMENTS, ");
			sbSql.append("       T.NULLABLE, ");
			sbSql.append("       CASE ");
			sbSql.append("         WHEN T2.POSITION IS NOT NULL THEN ");
			sbSql.append("          'Y' ");
			sbSql.append("         ELSE ");
			sbSql.append("          'N' ");
			sbSql.append("       END AS IS_PRIMARY_KEY ");
			sbSql.append("  FROM ALL_TAB_COLUMNS T, ");
			sbSql.append("       ALL_COL_COMMENTS T1, ");
			sbSql.append("       (SELECT C.OWNER, C.TABLE_NAME, D.POSITION, D.COLUMN_NAME ");
			sbSql.append("          FROM ALL_CONSTRAINTS C, ALL_CONS_COLUMNS D ");
			sbSql.append("         WHERE C.OWNER = D.OWNER ");
			sbSql.append("           AND C.CONSTRAINT_NAME = D.CONSTRAINT_NAME ");
			sbSql.append("           AND C.CONSTRAINT_TYPE = 'P' ");
			sbSql.append("           AND C.TABLE_NAME = ? ");
			sbSql.append("         ORDER BY C.TABLE_NAME, D.POSITION) T2 ");
			sbSql.append(" WHERE T.TABLE_NAME = ? ");
			sbSql.append("   AND T.TABLE_NAME = T1.TABLE_NAME ");
			sbSql.append("   AND T.OWNER = T1.OWNER ");
			sbSql.append("   AND T.TABLE_NAME = T1.TABLE_NAME ");
			sbSql.append("   AND T.COLUMN_NAME = T1.COLUMN_NAME ");
			sbSql.append("   AND T.COLUMN_NAME = T2.COLUMN_NAME(+) ");
			sbSql.append(" ORDER BY T.TABLE_NAME, T.COLUMN_ID ");
		} else if (database.equalsIgnoreCase("sql server")) {
			sbSql.append("SELECT t.name as 'table_name',  ");
			sbSql.append("	   c.column_id as 'column_id', ");
			sbSql.append("	   c.name as 'column_name', ");
			sbSql.append("       (CASE WHEN type_name(user_type_id) = 'datetime' THEN 'DATE' ");
			sbSql.append("             ELSE type_name(user_type_id)  ");
			sbSql.append("        END) as 'data_type', ");
			sbSql.append("        c.max_length as 'char_length', ");
			sbSql.append("       CAST(p.value AS VARCHAR(4000)) as 'comments', ");
			sbSql.append("        c.is_nullable as 'is_nullable', ");
			sbSql.append("       (CASE WHEN p1.primary_key_column is not null THEN 'Y' ELSE 'N' END) as 'is_primary_key' ");
			sbSql.append("  FROM sys.columns c ");
			sbSql.append(" INNER JOIN sys.objects t ON t.object_id = c.object_id ");
			sbSql.append("  LEFT OUTER JOIN sys.extended_properties p ");
			sbSql.append("            ON p.major_id = c.object_id AND p.minor_id = c.column_id AND p.name = 'MS_Description' ");
			sbSql.append("      LEFT OUTER JOIN (SELECT KU.table_name as table_name, ");
			sbSql.append("		column_name as primary_key_column ");
			sbSql.append("		FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ");
			sbSql.append("INNER JOIN ");
			sbSql.append("INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ");
			sbSql.append("ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND ");
			sbSql.append("TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME) p1 on p1.table_name = ? and c.name = p1.primary_key_column ");
			sbSql.append(" WHERE t.name= ? ");
			sbSql.append(" ORDER BY t.name, c.column_id ");
		}
		int count = 0;
		Object[] params = new Object[2];
		params[count++] = tableName;
		params[count++] = tableName;
		try {
			log.info("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			result = ((QueryResultObj)this.executeQuery(GenerateCodeWrapper.class, sbSql.toString(), params)).getQueryList();			
		} catch (Exception e) {
			log.error("由 " + Thread.currentThread().getStackTrace()[2] + " 呼叫執行 " + Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			throw e;
		}

		return result;	
	}

}
